﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using Npgsql;

namespace VoteInformationSystem.Models
{
    public class NpgsqlUeberhangmandateProParteiBundeslandRepository : IUeberhangmandateProParteiBundeslandRepository
    {
        public IEnumerable<UeberhangmandateProParteiBundesland> GetUeberhangmandateProParteiBundesland(int Jahr)
        {
            IList<UeberhangmandateProParteiBundesland> ueberhangmandateProParteiBundeslandListe = new List<UeberhangmandateProParteiBundesland>();

            using (NpgsqlConnection pgConnection = NpgsqlConnectionProvider.GetConnection())
            using (NpgsqlCommand command = new NpgsqlCommand("select \"Parteien\".pname, \"Bundeslaender\".bname, ueberhangmandate " +
            												 "from \"Ueberhangmandate\"(:jahr) join \"Parteien\" using (idpartei) join \"Bundeslaender\" using (idbundesland)",
            												 pgConnection))
            {
                command.Parameters.Add(new NpgsqlParameter("jahr", NpgsqlTypes.NpgsqlDbType.Integer));
                command.Prepare();
                command.Parameters.First<NpgsqlParameter>().Value = Jahr;
                using (NpgsqlDataReader dataReader = command.ExecuteReader())
                {
                    while (dataReader.Read())
                    {
                        ueberhangmandateProParteiBundeslandListe.Add(new UeberhangmandateProParteiBundesland() { Parteiname = dataReader.GetString(0), Bundeslandname = dataReader.GetString(1), Ueberhangmandate = dataReader.GetInt32(2) });
                    }
                }
            }

            return ueberhangmandateProParteiBundeslandListe;
        }
    }
}
